package com.scalar.chiptrack.operations.dataentry.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.Wafer;
import com.scalar.chiptrack.operations.release.SortRelease;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public class SortDataEntryDAO{


	public SortDataEntryDAO(){
	}

	public static ArrayList getBinInfo(int lotId,String waferId) throws ScalarException
   {
        Connection conn = null;
        Statement  stmt = null;
        ResultSet rs = null;

		ArrayList binList = new ArrayList();        
        
        try {
            conn = DataSource.getConnection();
            
			String sql = "select Bin_Number from Informative_Binning where Wafer1 = '"+waferId+"' and lot_id="+lotId;
            stmt = conn.createStatement ();
            rs = stmt.executeQuery(sql);

            while(rs.next()){
             binList.add(rs.getString(1));
            }
            
    
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get no.of sorts from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return binList;
  }

	public ArrayList getInfBinIdList(int lotId,String waferNumber,String waferId ) throws ScalarException  {


				 Connection conn = null;
				 ResultSet rs    = null;
				 Statement stmt=null;
				 String infBinId = "";
				 ArrayList infBinList = new ArrayList();

				 
                		
		try{
				
		
        conn = DataSource.getConnection();                    
		String sql = "select Inf_Bin_ID from Informative_Binning where "+waferNumber +" is not null  and lot_id="+lotId;
		//System.out.println("sql in getInfBinIdList "+sql);
        stmt = conn.createStatement ();
        rs = stmt.executeQuery(sql);

		

		while(rs.next()){
		infBinId =rs.getString(1);
		infBinList.add(infBinId);
		}
			
         if(infBinId.equals(""))
			infBinList = null;

		}
			catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not ged InfBinId's from database", sqle);
			}
			
			finally{
				 if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore){}
				}
				if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
				}
				}
			
			return infBinList;
		
        }	




public ArrayList getBinQty(int lotId,String waferNumber) throws ScalarException{


				 Connection conn = null;
				 ResultSet rs    = null;
				 Statement stmt=null;
				 String infBinId = "";
				 ArrayList binQuantityList = new ArrayList();
				 BinQuantity binQuantity = null;

				 
                		
		try{
				
		
        conn = DataSource.getConnection();                    
		String sql = "select Inf_Bin_ID,Bin_Qty from Informative_Binning where "+waferNumber +" is not null  and lot_id="+lotId;
		//System.out.println("sql in getInfBinIdList "+sql);
        stmt = conn.createStatement ();
        rs = stmt.executeQuery(sql);

		

		while(rs.next()){
        binQuantity = new BinQuantity();
		binQuantity.setInfBinId(rs.getString(1));
		binQuantity.setBinQuantity(rs.getString(2));
		binQuantityList.add(binQuantity);
		}
			        
	}
			catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Bin Quantity from database", sqle);
			}

				finally{
					if (stmt != null) {
					try { stmt.close(); } catch (SQLException ignore){}
					}
					if (conn != null) {
					try { conn.close(); } catch (SQLException ignore){}
					}
					}

			
			return binQuantityList;
		
        }	


 public ArrayList getWaferData(String lotId,String waferQuantity) throws ScalarException{
		Connection conn = null;
		ResultSet rs    = null;
		Statement stmt=null;
		Wafer wafer = null;
		ArrayList waferList = new ArrayList();
				 
                		
		try{
			conn = DataSource.getConnection();
			 		
			for(int i=1;i<=Integer.parseInt(waferQuantity);i++){

			String sql ="select distinct wafer"+i+", Pass,Remark,Yield,Total from  Informative_Binning where lot_id="+lotId+" and wafer"+i+" is not null";
			stmt = conn.createStatement();
			rs=stmt.executeQuery(sql);
			wafer = new Wafer();


			if(rs.next()){
			wafer.setWaferId(rs.getString(1));
			wafer.setPass(rs.getString(2));
			wafer.setRemark(rs.getString(3));
			wafer.setYield(rs.getString(4));
			wafer.setTotal(rs.getString(5));
			}
			waferList.add(wafer);
			stmt.close();
			}
		


		}catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get wafer data from database", sqle);
        }finally{
	 		if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return waferList;
		
	}	





public void saveWafer(int lotId,String waferId,String waferNumber, String remark,int noOfBins) throws ScalarException{


				 
				 Connection conn = null;
				 ResultSet rs    = null;
				 Statement stmt=null;
				 ArrayList infBinIdList = null;
				 String sql = "";
				              
				 		

		try{
		  
		  conn = DataSource.getConnection();    

		  infBinIdList=getInfBinIdList(lotId,waferNumber,waferId);

		  //System.out.println("infBinIdList "+infBinIdList);

		  if(infBinIdList !=null){
         
		  for(int i=0;i<infBinIdList.size();i++){ 
		
		  			
          int infBinId = Integer.parseInt((String)infBinIdList.get(i));
		  sql = "update Informative_Binning set Remark ='"+remark+"',"+waferNumber+"='"+waferId+"'  where Inf_Bin_ID ="+infBinId;
		  //System.out.println("Sql for update "+sql);
		  stmt = conn.createStatement();
          stmt.executeUpdate(sql);
		  stmt.close();
		  //System.out.println("Update Completed successfully");
		 			 
   		 }
		   conn.commit();	
			  
		  }
		  
		  else{
		  for(int i=0;i<noOfBins;i++){ 

          int binNo = i+1;	
		  sql = "Insert into Informative_Binning (Lot_ID,Bin_Number,"+waferNumber+",Remark) values("+lotId+","+binNo+",'"+waferId+"','"+remark+"')";	  
		  //System.out.println("Sql for Insert "+sql);	
		  stmt = conn.createStatement();
          stmt.executeUpdate(sql);
		  stmt.close();
   		  }

		  //System.out.println("Insertion Completed successfully");
		  conn.commit();
			
		 	 
		  
		 }

		}
	    	catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save wafer data", sqle);
        }

			finally{
            if (stmt != null) {
                try { stmt.close(); } catch (SQLException ignore){}
				}
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
				}
			}

        } 


	public static void updateAverageYield(DBTransaction dbTransaction,int lotId) throws ScalarException  {

		Connection conn = null;
		CallableStatement cs = null;

	

		try{
			 if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
            
						}

		//System.out.println("lotId "+lotId);            
		cs = conn.prepareCall("{call average_yield(?)}");
		cs.setInt(1,lotId);
		cs.execute();



	}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" Save Bin Qty failed", sql);
		}
           finally{             
			if(cs != null){
				try{ cs.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.commit();conn.close(); }catch(Exception e){}
			}

		   }

		}



	public void updateYield(DBTransaction dbTransaction,int lotId,float yield) throws ScalarException  {

		Connection conn = null;
		PreparedStatement pstmt = null;

	

		try{
			 if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
            
						}

		pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.sort.updateYield"));
		pstmt.setFloat(1, yield);
		pstmt.setInt(2,lotId);
		pstmt.executeUpdate();


	}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" Save Bin Qty failed", sql);
		}
          finally{            
			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
			}

		}


	public static void updateDieQtyOut(DBTransaction dbTransaction,int lotId,int processStepId) throws ScalarException  {

		Connection conn = null;
		CallableStatement cs = null;

	

		try{
			 if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
            
						}

		//System.out.println("lotId "+lotId);            
		cs = conn.prepareCall("{call sort_qty_out(?,?)}");
		cs.setInt(1,lotId);
		cs.setInt(2,processStepId);
		cs.execute();



	}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" updateDieQtyOut failed", sql);
		}
           finally{             
			if(cs != null){
				try{ cs.close(); }catch(Exception e){}
			}
			if(conn != null){
			   try{ conn.commit();conn.close(); }catch(Exception e){}
			}
		   }

		}
	
	
	

public static void saveBinQuantity(DBTransaction dbTransaction,int infBinId,String binQuantity,int binNumber,int pass,int total,float yield) throws  ScalarException  {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
	

		try{
			 if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
                        }
            
		//System.out.println("Bin Number in saveBinQty "+binNumber);
		pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.sort.updateBinQuantity"));
		pstmt.setString(1, binQuantity);
		pstmt.setInt(2,pass);
		pstmt.setInt(3,total);
		pstmt.setFloat(4,yield);
		pstmt.setInt(5, binNumber);
		pstmt.setInt(6, infBinId);
		pstmt.executeUpdate();


	
	}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException(" Save Bin Qty failed", sql);
		}
            finally{            
			if(pstmt != null){
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null){
				try{ conn.close(); }catch(Exception e){}
			}
			}

		}

    public static void saveSortLotBinDataEntryInSortBinReleaseTable( DBTransaction dbTransaction, int processStepId, int lotId, int goodBinsCount ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.dataentry.sort.saveSortBinLotDataInSortBinReleaseTable"));
            pstmt.setInt (1, processStepId );
            pstmt.setInt (2, lotId );
            pstmt.setInt (3, goodBinsCount );
            pstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save Sort Bin Lot Datap to database", sqle );
        }
        finally
        {

            if (dbTransaction == null)
            {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void closeSortReleases ( DBTransaction dbTransaction, int sortLotId ) throws ScalarException
    {
        Connection conn     = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.dataentry.sort.closeSortReleases" ) );
            cstmt.setInt (1, sortLotId );
            cstmt.execute();
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not Close sort releases", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    if ( success )
                    {
                        try
                        {
                            conn.commit();
                        }
                        catch ( SQLException e )
                        {
                            throw new ScalarException( "Could Not commit in closeSortReleases", e );
                        }
                    }
                    else
                    {
                        try
                        {
                            conn.rollback( );
                        }
                        catch( SQLException e )
                        {
                            throw new ScalarException( "Could Not rollback in closeSortReleases", e );
                        }
                    }
                }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
    }
   /**
    * This method is to get the sort Released Lot Numbers which will help to user
    * while entering the binning info in Sort Data entry for those lots
    * @param dbTransaction
    * @param processStepId
    * @param locationId
    * @return sortLotNumbersList
    * @throws ScalarException
    */

    public static ArrayList getSortReleaseLotNumbers( DBTransaction dbTransaction, String processStepId, String locationId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList sortLotNumbersList = new ArrayList();
        SortRelease sortRelease = null;

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall("{ call sp_load_sort_released_lots_for_dataentry( ?, ? ) }");
            cstmt.setString( 1, processStepId );
            cstmt.setString( 2, locationId );
            rset = cstmt.executeQuery();
            while( rset.next())
            {
                sortRelease = new SortRelease();
                sortRelease.setLotNumber( rset.getString("Lot_Number") );
                sortRelease.setWaferQty( rset.getInt( "Qty_Released") );
                sortRelease.setWaferId( rset.getString("Wafer_Ids") );
                sortLotNumbersList.add( sortRelease );
            }
        }
        catch(SQLException sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Error While getting the Sort Release Lot Details", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ){ }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); }catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch( SQLException ignore ) { }
            }
        }
        return sortLotNumbersList;
    }
}

